package com.cpecc.controller.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.context.ServletContextAware;

import com.cpecc.manager.system.DepManager;
import com.cpecc.manager.system.UserManager;
import com.cpecc.manager.wuzi.BaoFeiManager;
import com.cpecc.manager.wuzi.WuZiManager;
import com.cpecc.model.system.Dep;
import com.cpecc.model.system.User;
import com.cpecc.model.wuzi.BaoFei;
import com.cpecc.model.wuzi.WuZi;

@Controller
@RequestMapping(value = "/wuzi")
public class ExcelController implements ServletContextAware{

	@Autowired
	private ServletContext servletContext;
	
	@Override
	public void setServletContext(ServletContext context) {
		this.servletContext  = context;
	}
	
	@Autowired
	private WuZiManager wuZiManager;
	
	@Autowired
	private DepManager depManager;
	
	@Autowired
	private UserManager userManager;
	
	@Autowired
	private BaoFeiManager baoFeiManager;
	
	public void exportClassroom1(OutputStream os,List<WuZi> list) {
		try {
			WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
			WritableSheet wsheet = wbook.createSheet("物资总表", 0); // 工作表名称
			// 设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 12,
					WritableFont.NO_BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);
			String[] title = { "资产编号", "物资名称", "物资品牌", "规格型号", "产品序列号", "物资类别", "资产编码", "生产厂家",
					"供应商名称", "购买日期", "保修截止日期", "使用状态", "入库日期", "增加方式", "出库日期", "物资原值", "物资净值" ,"币种", "净残值率", "折旧月限", "报废月限", "折旧方式", "已折旧月数", "累计折旧金额","盘点状态", "存放地点", "管理部门","使用部门","使用人","备注"};
			// 设置Excel表头
			for (int i = 0; i < title.length; i++) {
				Label excelTitle = new Label(i, 0, title[i], titleFormat);
				wsheet.addCell(excelTitle);
			}
			for(int c=0;c<list.size();c++){				
				wsheet.addCell(new Label(0, c+1, list.get(c).getZiChanBH()));
				wsheet.addCell(new Label(1, c+1, list.get(c).getWuZiMC()));
				wsheet.addCell(new Label(2, c+1, list.get(c).getPinPai()));
				wsheet.addCell(new Label(3, c+1, list.get(c).getGuiGeXH()));
				wsheet.addCell(new Label(4, c+1, list.get(c).getChanPinXLH()));
				if(list.get(c).getWuZiLB()!=null)
					wsheet.addCell(new Label(5, c+1, list.get(c).getWuZiLB().getLeibieMC()));
				wsheet.addCell(new Label(6, c+1, list.get(c).getZiChanBM()));
				wsheet.addCell(new Label(7, c+1, list.get(c).getShengChanCJ()));
				wsheet.addCell(new Label(8, c+1, list.get(c).getGongYingShangMC()));
				if(list.get(c).getGouMaiRQ()!=null)
					wsheet.addCell(new Label(9, c+1, list.get(c).getGouMaiRQ().toString()));
				if(list.get(c).getBaoXiuJZRQ()!=null)
					wsheet.addCell(new Label(10, c+1, list.get(c).getBaoXiuJZRQ().toString()));
				if(list.get(c).getShiYongZT()!=null)
					wsheet.addCell(new Label(11, c+1, list.get(c).getShiYongZT().getZhangTaiMC()));
				if(list.get(c).getRuKuRQ()!=null)
					wsheet.addCell(new Label(12, c+1, list.get(c).getRuKuRQ().toString()));
				wsheet.addCell(new Label(13, c+1, list.get(c).getZengJiaFS()));
				if(list.get(c).getChuKuRQ()!=null)
					wsheet.addCell(new Label(14, c+1, list.get(c).getChuKuRQ().toString()));
				wsheet.addCell(new Label(15, c+1, Double.toString(list.get(c).getWuZiYZ())));
				wsheet.addCell(new Label(16, c+1, Double.toString(list.get(c).getWuZiJingZhi())));
				wsheet.addCell(new Label(17, c+1, list.get(c).getBiZhong().getBiZhongMC()));
				wsheet.addCell(new Label(18, c+1, list.get(c).getJingCanZhiLv()+"%"));
				wsheet.addCell(new Label(19, c+1, Integer.toString(list.get(c).getZheJiuYX())));
				wsheet.addCell(new Label(20, c+1, Integer.toString(list.get(c).getBaoFeiYX())));
				wsheet.addCell(new Label(21, c+1, list.get(c).getZheJiuFS() != null ? list.get(c).getZheJiuFS().getZheJiuFSMC() : null));
				wsheet.addCell(new Label(22, c+1, Integer.toString(list.get(c).getZheJiuYS())));
				wsheet.addCell(new Label(23, c+1, Double.toString(list.get(c).getLeiJiZJJE())));
				if(list.get(c).getPanDian()==0)
					wsheet.addCell(new Label(24, c+1, "盘点正常"));
				else if(list.get(c).getPanDian()==1)
					wsheet.addCell(new Label(24, c+1, "盘点中"));
				else if(list.get(c).getPanDian()==2)
					wsheet.addCell(new Label(24, c+1, "盘点丢失"));
				else if(list.get(c).getPanDian()==3)
					wsheet.addCell(new Label(24, c+1, "补办移交"));
				else if(list.get(c).getPanDian()==4)
					wsheet.addCell(new Label(24, c+1, "实物盘点"));
				else if(list.get(c).getPanDian()==5)
					wsheet.addCell(new Label(24, c+1, "盘点报废"));
				wsheet.addCell(new Label(25, c+1, list.get(c).getCunFangD()));
				if(list.get(c).getGuanLiBM()!=null)
					wsheet.addCell(new Label(26, c+1, list.get(c).getGuanLiBM().getDepName()));
				if(list.get(c).getDep()!=null)
					wsheet.addCell(new Label(27, c+1, list.get(c).getDep().getDepName()));
				if(list.get(c).getUser()!=null)
					wsheet.addCell(new Label(28, c+1, list.get(c).getUser().getName()));				
				wsheet.addCell(new Label(29, c+1, list.get(c).getBeiZhu()));
				
			}
			wbook.write(); // 写入文件
			wbook.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}
	
	public void exportClassroom2(OutputStream os,List<WuZi> list) {
		try {
			WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
			WritableSheet wsheet = wbook.createSheet("物资总表", 0); // 工作表名称
			// 设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 12,
					WritableFont.NO_BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);			
			String[] title = { "资产编号", "物资名称", "使用状态", "使用人", "使用部门", "管理部门", "物资品牌", "规格型号",
					"产品序列号", "保修截止日期", "物资类别", "资产编码","备注"};
			// 设置Excel表头
			for (int i = 0; i < title.length; i++) {
				Label excelTitle = new Label(i, 0, title[i], titleFormat);
				wsheet.addCell(excelTitle);
			}			
			for(int c=0;c<list.size();c++){				
				wsheet.addCell(new Label(0, c+1, list.get(c).getZiChanBH()));
				wsheet.addCell(new Label(1, c+1, list.get(c).getWuZiMC()));
				if(list.get(c).getShiYongZT()!=null)
					wsheet.addCell(new Label(2, c+1, list.get(c).getShiYongZT().getZhangTaiMC()));
				if(list.get(c).getUser()!=null)
					wsheet.addCell(new Label(3, c+1, list.get(c).getUser().getName()));
				if(list.get(c).getDep()!=null)
					wsheet.addCell(new Label(4, c+1, list.get(c).getDep().getDepName()));
				if(list.get(c).getGuanLiBM()!=null)
					wsheet.addCell(new Label(5, c+1, list.get(c).getGuanLiBM().getDepName()));
				wsheet.addCell(new Label(6, c+1, list.get(c).getPinPai()));	
				wsheet.addCell(new Label(7, c+1, list.get(c).getGuiGeXH()));
				wsheet.addCell(new Label(8, c+1, list.get(c).getChanPinXLH()));
				if(list.get(c).getBaoXiuJZRQ()!=null)
					wsheet.addCell(new Label(9, c+1, list.get(c).getBaoXiuJZRQ().toString()));
				if(list.get(c).getWuZiLB()!=null)
					wsheet.addCell(new Label(10, c+1, list.get(c).getWuZiLB().getLeibieMC()));
				wsheet.addCell(new Label(11, c+1, list.get(c).getZiChanBM()));				
				wsheet.addCell(new Label(12, c+1, list.get(c).getBeiZhu()));				
			}
			wbook.write(); // 写入文件
			wbook.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}
	
	public void exportClassroom3(OutputStream os,List<BaoFei> list) {
		try {
			WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
			WritableSheet wsheet = wbook.createSheet("物资总表", 0); // 工作表名称
			// 设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 12,
					WritableFont.NO_BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);
			String[] title = { "资产编号", "物资名称", "物资品牌", "规格型号", "产品序列号", "物资类别", "资产编码", "生产厂家",
					"供应商名称", "购买日期", "保修截止日期", "使用状态", "入库日期", "增加方式", "出库日期", "物资原值", "物资净值" ,"币种", "净残值率", "折旧月限", "报废月限", "折旧方式", "已折旧月数", "累计折旧金额","盘点状态", "存放地点", "管理部门","使用部门","使用人","备注","申请部门","申请人"};
			// 设置Excel表头
			for (int i = 0; i < title.length; i++) {
				Label excelTitle = new Label(i, 0, title[i], titleFormat);
				wsheet.addCell(excelTitle);
			}
			for(int c=0;c<list.size();c++){				
				wsheet.addCell(new Label(0, c+1, list.get(c).getWuZi().getZiChanBH()));
				wsheet.addCell(new Label(1, c+1, list.get(c).getWuZi().getWuZiMC()));
				wsheet.addCell(new Label(2, c+1, list.get(c).getWuZi().getPinPai()));
				wsheet.addCell(new Label(3, c+1, list.get(c).getWuZi().getGuiGeXH()));
				wsheet.addCell(new Label(4, c+1, list.get(c).getWuZi().getChanPinXLH()));
				if(list.get(c).getWuZi().getWuZiLB()!=null)
					wsheet.addCell(new Label(5, c+1, list.get(c).getWuZi().getWuZiLB().getLeibieMC()));
				wsheet.addCell(new Label(6, c+1, list.get(c).getWuZi().getZiChanBM()));
				wsheet.addCell(new Label(7, c+1, list.get(c).getWuZi().getShengChanCJ()));
				wsheet.addCell(new Label(8, c+1, list.get(c).getWuZi().getGongYingShangMC()));
				if(list.get(c).getWuZi().getGouMaiRQ()!=null)
					wsheet.addCell(new Label(9, c+1, list.get(c).getWuZi().getGouMaiRQ().toString()));
				if(list.get(c).getWuZi().getBaoXiuJZRQ()!=null)
					wsheet.addCell(new Label(10, c+1, list.get(c).getWuZi().getBaoXiuJZRQ().toString()));
				if(list.get(c).getWuZi().getShiYongZT()!=null)
					wsheet.addCell(new Label(11, c+1, list.get(c).getWuZi().getShiYongZT().getZhangTaiMC()));
				if(list.get(c).getRuKuRQ()!=null)
					wsheet.addCell(new Label(12, c+1, list.get(c).getWuZi().getRuKuRQ().toString()));
				wsheet.addCell(new Label(13, c+1, list.get(c).getWuZi().getZengJiaFS()));
				if(list.get(c).getWuZi().getChuKuRQ()!=null)
					wsheet.addCell(new Label(14, c+1, list.get(c).getWuZi().getChuKuRQ().toString()));
				wsheet.addCell(new Label(15, c+1, Double.toString(list.get(c).getWuZi().getWuZiYZ())));
				wsheet.addCell(new Label(16, c+1, Double.toString(list.get(c).getWuZi().getWuZiJingZhi())));
				wsheet.addCell(new Label(17, c+1, list.get(c).getWuZi().getBiZhong().getBiZhongMC()));
				wsheet.addCell(new Label(18, c+1, list.get(c).getWuZi().getJingCanZhiLv()+"%"));
				wsheet.addCell(new Label(19, c+1, Integer.toString(list.get(c).getWuZi().getZheJiuYX())));
				wsheet.addCell(new Label(20, c+1, Integer.toString(list.get(c).getWuZi().getBaoFeiYX())));
				wsheet.addCell(new Label(21, c+1, list.get(c).getWuZi().getZheJiuFS().getZheJiuFSMC()));
				wsheet.addCell(new Label(22, c+1, Integer.toString(list.get(c).getWuZi().getZheJiuYS())));
				wsheet.addCell(new Label(23, c+1, Double.toString(list.get(c).getWuZi().getLeiJiZJJE())));
				if(list.get(c).getWuZi().getPanDian()==0)
					wsheet.addCell(new Label(24, c+1, "盘点正常"));
				else if(list.get(c).getWuZi().getPanDian()==1)
					wsheet.addCell(new Label(24, c+1, "盘点中"));
				else if(list.get(c).getWuZi().getPanDian()==2)
					wsheet.addCell(new Label(24, c+1, "盘点丢失"));
				else if(list.get(c).getWuZi().getPanDian()==3)
					wsheet.addCell(new Label(24, c+1, "补办移交"));
				else if(list.get(c).getWuZi().getPanDian()==4)
					wsheet.addCell(new Label(24, c+1, "实物盘点"));
				else if(list.get(c).getWuZi().getPanDian()==5)
					wsheet.addCell(new Label(24, c+1, "盘点报废"));
				else if(list.get(c).getWuZi().getPanDian()==8)
					wsheet.addCell(new Label(24, c+1, "盘点停止"));
				wsheet.addCell(new Label(25, c+1, list.get(c).getWuZi().getCunFangD()));
				if(list.get(c).getWuZi().getGuanLiBM()!=null)
					wsheet.addCell(new Label(26, c+1, list.get(c).getWuZi().getGuanLiBM().getDepName()));
				if(list.get(c).getWuZi().getDep()!=null)
					wsheet.addCell(new Label(27, c+1, list.get(c).getWuZi().getDep().getDepName()));
				if(list.get(c).getWuZi().getUser()!=null)
					wsheet.addCell(new Label(28, c+1, list.get(c).getWuZi().getUser().getName()));				
				wsheet.addCell(new Label(29, c+1, list.get(c).getWuZi().getBeiZhu()));
				wsheet.addCell(new Label(30, c+1, list.get(c).getUser().getDep().getDepName()));
				wsheet.addCell(new Label(31, c+1, list.get(c).getUser().getName()));
			}
			wbook.write(); // 写入文件
			wbook.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//全部物资
	@RequestMapping(value = "/daoChu1")
	public void daoChu1(WuZi wuZi,String shiYongdepId,String shiYonguserId, String wuZiLBId,int wuZiZTId,HttpServletResponse response){
		String path = this.servletContext.getRealPath("/upload/");
		String xinName = "";
		String format="yyyyMMdd";
		SimpleDateFormat sdf=new SimpleDateFormat(format);
		String rq=sdf.format(new Date());
		xinName+=rq;
		xinName += new Date().getTime();
		File file = new File(path, xinName + ".xls");
		try{
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);
			List<WuZi> list=wuZiManager.getWuZiList(wuZi,wuZiLBId,wuZiZTId,shiYongdepId,shiYonguserId);
			exportClassroom1(os,list);
			PrintWriter out = null;
			//response.setContentType("application/json");
			String result="/upload/"+xinName+".xls";			
			out = response.getWriter();
			out.write(result);			
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//部门物资
	@RequestMapping(value = "/daoChu2")
	public void daoChu2(WuZi wuZi,String wuZiLBId,int wuZiZTId,HttpServletResponse response){
		String path = this.servletContext.getRealPath("/upload/");
		String xinName = "";
		String format="yyyyMMdd";
		SimpleDateFormat sdf=new SimpleDateFormat(format);
		String rq=sdf.format(new Date());
		xinName+=rq;
		xinName += new Date().getTime();
		File file = new File(path, xinName + ".xls");
		try{
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);
			Subject currentUser = SecurityUtils.getSubject();
			Object obj=currentUser.getPrincipals();
			String name=obj.toString();
			User user=userManager.findByUsername(name);
			Dep dep=user.getDep();
			List<WuZi> list=wuZiManager.getBuMenWuZiListCX(dep,wuZi,wuZiLBId,wuZiZTId);
			exportClassroom1(os,list);
			PrintWriter out = null;
			//response.setContentType("application/json");
			String result="/upload/"+xinName+".xls";			
			out = response.getWriter();
			out.write(result);			
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//库存物资
	@RequestMapping(value = "/daoChu3")
	public void daoChu3(WuZi wuZi,String wuZiLBId,HttpServletResponse response){
		String path = this.servletContext.getRealPath("/upload/");
		String xinName = "";
		String format="yyyyMMdd";
		SimpleDateFormat sdf=new SimpleDateFormat(format);
		String rq=sdf.format(new Date());
		xinName+=rq;
		xinName += new Date().getTime();
		File file = new File(path, xinName + ".xls");
		try{
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);			
			List<WuZi> list=wuZiManager.getWuZiList(wuZi, wuZiLBId, new int[]{105, 107});
			exportClassroom1(os,list);
			PrintWriter out = null;
			//response.setContentType("application/json");
			String result="/upload/"+xinName+".xls";			
			out = response.getWriter();
			out.write(result);			
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//导入扫码器
	@RequestMapping(value = "/daoChu4")
	public void daoChu4(WuZi wuZi,
			@RequestParam(required = false) String depId,
			@RequestParam(required = false) String userId,
			HttpServletResponse response) {
		String path = this.servletContext.getRealPath("/upload/");
		String xinName = "";
		String format = "yyyyMMdd";
		SimpleDateFormat sdf = new SimpleDateFormat(format);
		String rq = sdf.format(new Date());
		xinName += rq;
		xinName += new Date().getTime();
		File file = new File(path, xinName + ".xls");
		try {
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);
			List<WuZi> list = wuZiManager.getYiChangList(wuZi, depId, userId);
			exportClassroom2(os, list);
			PrintWriter out = null;
			// response.setContentType("application/json");
			String result = "/upload/" + xinName + ".xls";
			out = response.getWriter();
			out.write(result);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//库存物资
	@RequestMapping(value = "/daoChu5")
	public void daoChu5(WuZi wuZi,@RequestParam(required=false)String depId,@RequestParam(required=false)String userId,HttpServletResponse response){
		String path = this.servletContext.getRealPath("/upload/");
		String xinName = "";
		String format="yyyyMMdd";
		SimpleDateFormat sdf=new SimpleDateFormat(format);
		String rq=sdf.format(new Date());
		xinName+=rq;
		xinName += new Date().getTime();
		File file = new File(path, xinName + ".xls");
		try{
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);			
			List<WuZi> list=wuZiManager.getYiChangList(wuZi,depId,userId);
			exportClassroom1(os,list);
			PrintWriter out = null;
			//response.setContentType("application/json");
			String result="/upload/"+xinName+".xls";			
			out = response.getWriter();
			out.write(result);
			
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//报废库物资
	@RequestMapping(value = "/daoChu6")
	public void daoChu6(WuZi wuZi,@RequestParam(required = false) String wuZiLBId,HttpServletResponse response) {
		String path = this.servletContext.getRealPath("/upload/");
		String xinName = "";
		String format = "yyyyMMdd";
		SimpleDateFormat sdf = new SimpleDateFormat(format);
		String rq = sdf.format(new Date());
		xinName += rq;
		xinName += new Date().getTime();
		File file = new File(path, xinName + ".xls");
		try {
			file.createNewFile();
			OutputStream os = new FileOutputStream(file);
			List<BaoFei> list=baoFeiManager.getAllBaoFeiKu(wuZi,wuZiLBId);			
			exportClassroom3(os, list);
			PrintWriter out = null;
			// response.setContentType("application/json");
			String result = "/upload/" + xinName + ".xls";
			out = response.getWriter();
			out.write(result);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}
